﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections.Generic;
using nsBaseClass;
using log4net;
using eServiceAvailability.NetUtils.Database.Model;

namespace eServiceAvailability.NetUtils.Database
{
    public class DBAccess
    {
        protected static readonly ILog _log = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        public static string test()
        {
            try
            {
                var hSql = new clsSqlFactory();
                var objUtil = new clsBaseUtility();
                hSql.NewCommand(" select count(*) from " + objUtil.getTable("CORW"));

                if (hSql.ExecuteReader() && hSql.Read())
                {
                    return hSql.Reader.GetString(0);
                }
                else
                    return string.Empty;

            }
            catch (Exception ex)
            {
                _log.Error(ex.Message);
                return string.Empty;
            }

        }

        

        public static List<TimeSlot> FindAvailability(string type, string workshopid, string day)
        {
            List<TimeSlot> list = new List<TimeSlot>();
            try
            {
                var hSql = new clsSqlFactory();
                var objUtil = new clsBaseUtility();

                string sqlEsbookh = "SELECT handover  FROM	ESBOOKH	WHERE	SERVDATE = CONVERT (datetime, '{0}', 102)  AND BSTATUS != 5";// 102='yyyy.MM.dd'
                sqlEsbookh = string.Format(sqlEsbookh, day);
                if (!string.IsNullOrEmpty(workshopid))
                {
                    sqlEsbookh+= " AND WORKSHOPID = '{0}'";
                    sqlEsbookh = string.Format(sqlEsbookh, workshopid);
                
                }


                // string sql = "SELECT C1 AS Code,C2 AS Description ,C3 AS StartTime,C4 AS EndTime FROM CORW WHERE C1 NOT IN ({0}) AND CODAID = 'eshandovme' AND V1 = {1};";
                string sql = "SELECT ISNULL(C1,''),ISNULL(C2,'') ,ISNULL(C3,''),ISNULL(C4,'') FROM CORW WHERE C1 NOT IN ({0}) AND CODAID = 'eshandovme' ";
                sql = string.Format(sql, sqlEsbookh);
                if (!string.IsNullOrEmpty(type))
                {
                    sql += " AND V1 = {0} ";
                    sql = string.Format(sql, type);
                }

                _log.Debug(sql);

                hSql.NewCommand(sql);

                if (hSql.ExecuteReader())
                {
                    while (hSql.Read())
                    {
                        var code = hSql.Reader.GetValue(0);
                        var description = hSql.Reader.GetValue(1);
                        var start = hSql.Reader.GetValue(2);
                        var end = hSql.Reader.GetValue(3);
                        TimeSlot ret = new TimeSlot(code.ToString(), description.ToString(), start.ToString(), end.ToString());
                        list.Add(ret);
                    }
                }
                hSql.Close();
                return list;

            }
            catch (Exception ex)
            {
                _log.Error(ex.Message);
                return list;
            }

        }


        //------
    }
}
